package com.ld.hpfs.web.controller;

import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;
import org.apache.log4j.chainsaw.Main;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ld.hpfs.common.jackson.JackJson;
import com.ld.hpfs.common.utils.Const;
import com.ld.hpfs.common.utils.Tools;
import com.ld.hpfs.dao.DbSourceOuttingMapper;
import com.ld.hpfs.pojo.DbSourceOutting;
import com.ld.hpfs.pojo.DbSourceSetting;
import com.ld.hpfs.pojo.ExtPager;
import com.ld.hpfs.pojo.ExtReturn;
import com.ld.hpfs.pojo.User;
import com.ld.hpfs.service.DbSourceOuttingService;
import com.ld.hpfs.service.ExportService;
import com.ld.hpfs.service.HistoryService;
import com.ld.hpfs.service.UploadService;
import com.ld.hpfs.web.interseptor.WebConstants;

@Controller
public class DBOuttingController {
    private static final Logger    logger = LoggerFactory.getLogger(LoginController.class);

    @Autowired
    private DbSourceOuttingService dbouttingService;
    @Autowired
    private DbSourceOuttingMapper dbsourceOuttingMapper;
    @Autowired
    private UploadService          uploadService;
    @Autowired
	private ExportService exportService;
    
    //用于添加history表
    @Autowired
    private HistoryService          historyService;
 
    // 查询dbsetting 列表
    @RequestMapping(value = "/getdbouttings")
    @ResponseBody
    public void getdbsetting(ExtPager page, HttpSession session, HttpServletRequest request,PrintWriter writer) {

        Object currentCompany = session.getAttribute(WebConstants.CURRENT_COMPANY);
//        if (currentCompany == null) {
//            return new ExtReturn(false, "请先登录!");
//        }
        //String sort = (String) request.getParameter("sort") == null ? null : (String) request.getParameter("sort");
        try {
        int pageStart = page.getStart();
        int pageEnd = page.getStart() + page.getLimit();
        String orderby = "DS_NAME";      
        //String direction = "ASC";
        ArrayList<Map<String, String>> field = page.getSortField();
		StringBuffer sort = new StringBuffer("");
		User user = (User)session.getAttribute(WebConstants.CURRENT_USER);				
		  if(user !=null){
			  if(field!=null && field.size()>0){
		    for(Map<String, String> map : field){
		    	String property = map.get("property");
		    	if (property.equals("ds_name"))
		    	{
		    		property = "DS_NAME";
		    	}else if (property.equals("dbtype"))
		    	{
		    		property = "DS_TYPE";
		    	}else if (property.equals("ds_db_name"))
		    	{
		    		property = "ds_db_name";
		    	}else if (property.equals("ds_db_sid"))
		    	{
		    		property = "ds_db_sid";
		    	}else if (property.equals("ds_username"))
		    	{
		    		property = "ds_username";
		    	}else if (property.equals("ds_password"))
		    	{
		    		property = "ds_password";
		    	}else if (property.equals("ds_ip"))
		    	{
		    		property = "ds_ip";
		    	}else if (property.equals("ds_port"))
		    	{
		    		property = "ds_port";
		    	}else if (property.equals("ds_connect_str"))
		    	{
		    		property = "ds_connect_str";
		    	}else if (property.equals("hversion"))
		    	{
		    		property = "hversion";
		    	}else if (property.equals("sql_str"))
		    	{
		    		property = "sql_str";
		    	}
		    	sort.append(property + " " + map.get("direction") + ",");
		    }
		    orderby = sort.substring(0,sort.length()-1);
		} 

        HashMap<String, Object> param = new HashMap<String, Object>();
        HashMap<String,Object> result = new HashMap<String,Object>();
        param.put("pageStart", pageStart);
        param.put("pageEnd", pageEnd);
        param.put("orderBy", orderby);

        param.put("id", Integer.parseInt(currentCompany.toString()));
        // currentCompany = "1";
        ArrayList<HashMap<String,Object>> logs = dbouttingService.getDbSourceOuttings(param);
		if(logs!=null&&logs.size()>0){
			result.put("success", true);
			result.put("total", param.get("pageCount"));
			result.put("data", logs);
		} else {
			result.put("success", true);
			result.put("total", param.get("pageCount"));
			result.put("data", "");
		}
		String returnMsg = JackJson.fromObjectToJson(result);
		logger.debug("**********EXPORT_DATA : {}**********", returnMsg);
		writer.print(returnMsg);
	 }else{
 		writer.print("");
	  }
	} catch (Exception e) {
		logger.error("Exception: ", e);
	} finally {
		writer.flush();
		writer.close();
	}

    }

    private String formatDBOuttingResult(List<DbSourceOutting> outtings, int count) {
        StringBuilder sb = new StringBuilder();
        sb.append("{");
        sb.append("\r\n");
        sb.append("\"success\": true,");
        sb.append("\r\n");
        sb.append("\"total\":").append(count).append(",");
        sb.append("\r\n");
        sb.append("\"children\": [");
        sb.append("\r\n");
        if (outtings != null) {
            for (int i = 0; i < outtings.size(); i++) {
            	DbSourceOutting outting = outtings.get(i);
                int ds_hid = outting.getDs_hid();
                int hversion = outting.getHversion();
                String ds_name = outting.getDs_name();
                String ds_type = outting.getDs_type();
                String ds_db_name = outting.getDs_db_name();
                String ds_db_sid = outting.getDs_db_sid();
                String ds_username = outting.getDs_username();
                String ds_password = outting.getDs_password();
                String ds_ip = outting.getDs_ip();
                String ds_port = outting.getDs_port();
                String ds_connect_str = outting.getDs_connect_str();
                String sql_str = outting.getSql_str();
                String creator_id = outting.getCreator_id();
                String create_time = outting.getCreate_time();
                String ref_company = outting.getRef_company();

  

                sb.append("{\"ds_hid\":\"").append(ds_hid).append("\",").append("\"hversion\":\"").append(hversion).append("\",")
                        .append("\"ds_name\":\"").append(ds_name).append("\",").append("\"ds_type\":\"").append(ds_type).append("\",")
                        .append("\"ds_db_name\":\"") .append(ds_db_name).append("\",").append("\"ds_db_sid\":\"").append(ds_db_sid).append("\",")
                        .append("\"ds_username\":\"").append(ds_username).append("\",").append("\"ds_password\":\"").append(ds_password).append("\",")
                        .append("\"ds_ip\":\"").append(ds_ip).append("\",").append("\"ds_port\":\"").append(ds_port).append("\",")
                        .append("\"ds_connect_str\":\"").append(ds_connect_str).append("\",").append("\"sql_str\":\"").append(sql_str).append("\",")
                        .append("\"creator_id\":\"").append(creator_id).append("\",").append("\"create_time\":\"").append(create_time).append("\",")
                        .append("\"ref_company\":\"").append(ref_company).append("\"")
                        .append("}");

                if (i < outtings.size() - 1) {
                    sb.append(",");
                }

                sb.append("\r\n");
            }
        }
        sb.append("]");
        sb.append("\r\n");
        sb.append("}");
        logger.debug("************ExportDATA：{}************",sb.toString());
        return sb.toString();
    }
    
    @RequestMapping(value = "/adddboutting")
    @ResponseBody
    public Object adddbouttingfields(@RequestParam String hversion,
            @RequestParam String ds_name, @RequestParam String ds_type, @RequestParam String ds_db_name,
            @RequestParam String ds_db_sid, @RequestParam String ds_username, @RequestParam String ds_password,
            @RequestParam String ds_ip, @RequestParam String ds_port,
            @RequestParam String sql_str, HttpSession session,
            HttpServletRequest request) {

        User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
//        Object currentCompany = session.getAttribute(WebConstants.CURRENT_COMPANY);
        
        DbSourceOutting dboutting = new DbSourceOutting();
        String connectString = this.getConnectString(ds_type, ds_ip, ds_port, ds_db_sid);
        // dbsetting.setCompanyId(currentCompany);
       // dbsetting.setCompanyId(Integer.parseInt(currentCompany.toString()));
        // here to be modify
        

		//dboutting.setHversion(Integer.parseInt(hversion));
		dboutting.setDs_name(ds_name);
		dboutting.setDs_type(ds_type);
		dboutting.setDs_db_name(ds_db_name);
		dboutting.setDs_db_sid(ds_db_sid);
		dboutting.setDs_username(ds_username);
		dboutting.setDs_password(ds_password);
		dboutting.setDs_ip(ds_ip);
		dboutting.setDs_port(ds_port);
		dboutting.setDs_connect_str(connectString);
		dboutting.setSql_str(sql_str);
		dboutting.setCreator_id(user.getUserId());
        


        int iResult = dbouttingService.addDbSourceOutting(dboutting);
        return new ExtReturn(true, "success");
    }
    
    //
    private String getConnectString(String ds_type, String ds_ip, String dbport, String ds_db_sid) {
        if (ds_type == null) {
        	ds_type = "sqlserver";
        }

        StringBuffer jdbcurl = null;

        if (ds_type.equals("oracle")) {

            jdbcurl = new StringBuffer("jdbc:oracle:thin:@");
            jdbcurl.append(ds_ip).append(":").append(dbport).append(":").append(ds_db_sid);
        } else // sqlserver
        {
            jdbcurl = new StringBuffer("jdbc:jtds:sqlserver://");
            jdbcurl.append(ds_ip).append(":").append(dbport).append(";DatabaseName=").append(ds_db_sid);

        }

        return jdbcurl.toString();
    }   
    
    @RequestMapping(value = "/updatedboutting")
    @ResponseBody
    public Object updatedbsettingfields(@RequestParam String hversion,
            @RequestParam String ds_name, @RequestParam String ds_type, @RequestParam String ds_db_name,
            @RequestParam String ds_db_sid, @RequestParam String ds_username, @RequestParam String ds_password,
            @RequestParam String ds_ip, @RequestParam String ds_port,
            @RequestParam String sql_str, @RequestParam String ds_hid,
            HttpSession session, HttpServletRequest request) {

        //Object currentCompany = session.getAttribute(WebConstants.CURRENT_COMPANY);
        DbSourceOutting dboutting = new DbSourceOutting();
        String connectString = this.getConnectString(ds_type, ds_ip, ds_port, ds_db_sid);
        // dbsetting.setCompanyId(currentCompany);
        // here to be modify
		//dboutting.setHversion(Integer.parseInt(hversion));
		dboutting.setDs_name(ds_name);
		dboutting.setDs_type(ds_type);
		dboutting.setDs_db_name(ds_db_name);
		dboutting.setDs_db_sid(ds_db_sid);
		dboutting.setDs_username(ds_username);
		dboutting.setDs_password(ds_password);
		dboutting.setDs_ip(ds_ip);
		dboutting.setDs_port(ds_port);
		dboutting.setDs_connect_str(connectString);
		dboutting.setSql_str(sql_str);
		
		dboutting.setDs_hid(Integer.parseInt(ds_hid));

		// 用于添加history表
	     HashMap<String, Object> hist_param = new HashMap<String, Object>();
	     hist_param.put("id", Integer.parseInt(ds_hid));
	     User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
	     if(user != null){
	    	 hist_param.put("update_id", Integer.parseInt(user.getUserId()));
	    	 hist_param.put("update_name", user.getAccount());
	     }
	     historyService.addOutputDbSourceHistory(hist_param, true);
		
        int iResult = dbouttingService.updateDbSourceOutting(dboutting);
        return new ExtReturn(true, "success");
    }
    
    @RequestMapping(value = "/deletedbouttings")
    @ResponseBody
    public Object deletedbsettings(@RequestParam String ids, HttpSession session, HttpServletRequest request) {
        if (ids != null && ids.length() > 0) {
            StringTokenizer st = new StringTokenizer(ids, "|");
            String[] idlist = new String[st.countTokens()];

         // 用于添加history表
         	HashMap<String, Object> param = new HashMap<String, Object>();
         	User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
         	if(user != null){
         		param.put("update_id", Integer.parseInt(user.getUserId()));
         		param.put("update_name", user.getAccount());
         	}
            
            int i = 0;
            while (st.hasMoreTokens()) {
                idlist[i++] = st.nextToken();
            }
            for (i = 0; i < idlist.length; i++) {
            	
            	// 用于添加history表
            	param.put("id", Integer.parseInt(idlist[i]));
            	historyService.addOutputDbSourceHistory(param, false);
            	
                dbouttingService.deleteDbSourceOutting(Integer.parseInt(idlist[i]));
            }
        }

        return new ExtReturn(true, "success");
    }
    
    
    @RequestMapping(value = "/testdboutting")
    @ResponseBody
    public Object testdboutting(@RequestParam String hversion,
            @RequestParam String ds_name, @RequestParam String ds_type, @RequestParam String ds_db_name,
            @RequestParam String ds_db_sid, @RequestParam String ds_username, @RequestParam String ds_password,
            @RequestParam String ds_ip, @RequestParam String ds_port,
            @RequestParam String sql_str, @RequestParam String ds_hid,
            HttpSession session, HttpServletRequest request) {

        //Object currentCompany = session.getAttribute(WebConstants.CURRENT_COMPANY);
        DbSourceOutting dboutting = new DbSourceOutting();
        String connectString = this.getConnectString(ds_type, ds_ip, ds_port, ds_db_sid);
        // dbsetting.setCompanyId(currentCompany);
        // here to be modify
		//dboutting.setHversion(Integer.parseInt(hversion));
		

		HashMap<String,Object> param = new HashMap<String,Object>();
		//int id = (Integer)session.getAttribute(WebConstants.CURRENT_COMPANY);		
		param.put("table_name", "TAX_V_ID_HEAD_ACK");

		List<HashMap<String,Object>> list = exportService.getExportExcelListOne(param);
		
		
		String driver = null;
        java.sql.Connection dbConn = null;
        Statement sta = null;
        
		if (ds_type.equals("oracle")) {
            driver = "oracle.jdbc.driver.OracleDriver";
        } else // sqlserver
        {
            driver = "net.sourceforge.jtds.jdbc.Driver";
        }
		
		String message = "测试成功";
         
         try {

             Class.forName(driver);

             dbConn = DriverManager.getConnection(connectString, ds_username, ds_password);

             sta = dbConn.createStatement();
				String sql = sql_str;
				int pos = 0;
	        	int cnt2 = 0;
	    		while (pos < sql.length()) {
	    			pos = sql.indexOf("{", pos);
	    			cnt2 = sql.indexOf("}", pos);
	    			if (pos == -1) {
	    				break;
	    			} else {
	    				String sub2 = sql.substring(pos+1, cnt2);
	    				cnt2++;
	    				String sub = sql.substring(pos, cnt2);
	    				boolean col_name = false;
	    				for(int i=0;i<list.size();i++){
	    					HashMap<String, Object> beanMap = list.get(i);
	    					if(sub2.equals(beanMap.get("name"))){
	    						col_name = true;
	    					}		
	    				}
	    				
	    				if(!col_name){
	    					message = "字段{"+sub2+"}不存在";
	    					return new ExtReturn(false, message);
	    				}
	    				sql =sql.replace(sub, "'"+sub2+"'");
	    			}
	    		}
     		
         } catch (Exception e) {
             e.printStackTrace();
             message = e.getMessage();
         } finally {
             try {
                 if (sta != null) {
                     sta.close();
                 }
                 if (dbConn == null) {
                	 message = "数据库链接失败，请检查数据库类型，ip，用户名及密码";
                 }
                 if (dbConn != null) {
                     dbConn.close();
                 }
                 
             } catch (Exception ex) {
             	
             }
         }
         if("测试成功".equals(message)){
         	return new ExtReturn(false, message);
         }else {
         	return new ExtReturn(false, "测试失败："+message);
 		}
    }
    
    @RequestMapping(value = "/setQuoteExportSource",method = RequestMethod.POST)
	public void setQuoteMain(HttpSession session,HttpServletRequest request,HttpServletResponse response,PrintWriter writer){
		String Ds_Hid = (String)request.getParameter("Ds_Hid") == null ? null : (String)request.getParameter("Ds_Hid");
		HashMap<String,Object> result = new HashMap<String,Object>();
		response.setContentType("text/plain;charset=UTF-8");		
		try {	
			String company_id = session.getAttribute(WebConstants.CURRENT_COMPANY).toString();
			User user = (User)session.getAttribute(WebConstants.CURRENT_USER);
			if(user != null){
				HashMap<String, Object> param = new HashMap<String, Object>();				
				param.put("Ds_Hid", Ds_Hid);
				param.put("company_id", company_id);
				int ret = dbouttingService.setQuoteExportSource(param);
				if(ret == 0){
					result.put("resultcode",0);
				}else{
					result.put("resultcode",9);
				}
			}else{
		 		writer.print("");
			  }
			writer.print(JackJson.fromObjectToJson(result));
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	 }
    
}
